﻿using jinmaMesProxy.Excep;
using Microsoft.Data.SqlClient;
using System.Data;
using System.Data.Common;

namespace jinmaMesProxy.DataAccess
{
    public class ErpDbInstance : IDisposable
    {
        ErpConfig config;
        SqlConnection dbConnection;
        static ErpDbInstance? mainInstance;
        SqlTransaction? dbTransaction;
        static object locker = new object();
        static object locker2 = new object();
        public static ErpDbInstance CreateDefaultInstance()
        {
            lock (locker)
            {
                lock (locker2)
                {
                    if (mainInstance == null)
                    {
                        mainInstance = new ErpDbInstance(ErpConfig.Default());
                    }
                    return mainInstance;
                }
            }
        }
        public ErpDbInstance()
        {
            config = ErpConfig.Default();
            Open();
        }
        public ErpDbInstance(ErpConfig config)
        {
            this.config = config;
            Open();
        }
        public void Open()
        {
            if (dbConnection == null)
            {
                var sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
                sqlConnectionStringBuilder.DataSource = config.server;
                sqlConnectionStringBuilder.UserID = config.uid;
                sqlConnectionStringBuilder.Password = config.pwd;
                sqlConnectionStringBuilder.InitialCatalog = config.database;
                sqlConnectionStringBuilder.Encrypt = false;
                sqlConnectionStringBuilder.ApplicationName = "jinmaMes";
                sqlConnectionStringBuilder.ConnectTimeout = 30;
                sqlConnectionStringBuilder.MaxPoolSize = 100;
                sqlConnectionStringBuilder.MinPoolSize = 2;
                sqlConnectionStringBuilder.Pooling = true;
                sqlConnectionStringBuilder.TrustServerCertificate = true;
                var str = sqlConnectionStringBuilder.ToString();
                dbConnection = new SqlConnection(str);
            }
            if (dbConnection.State == ConnectionState.Closed)
            {
                try
                {
                    dbConnection.Open();
                    Logger.Log("数据库[{0}|{1}]已被打开", config.server, config.uid);
                }
                catch (Exception ex)
                {
                    throw new MyException(ex);
                }
            }
        }
        public void Close()
        {
            if (dbConnection != null)
            {
                try
                {
                    dbConnection.Close();
                    dbConnection.Dispose();
                    Logger.Log("数据库[{0}|{1}]已被关闭", config.server, config.uid);
                }
                catch (Exception ex)
                {
                    throw new MyException(ex);
                }
            }
        }

        public ErpDbInstance StartTransaction()
        {
            if (dbConnection == null || dbConnection.State != ConnectionState.Open)
            {
                throw new MyException("无法启动事务，数据库尚未连接");
            }
            try
            {
                dbTransaction = dbConnection.BeginTransaction();
            }
            catch (Exception ex)
            {
                throw new MyException(ex);
            }
            return this;
        }
        public SqlCommand MakeQueryCommand(string sqlStr, params KeyValuePair<string, object?>[] p)
        {
            SqlCommand cmd = dbConnection!.CreateCommand();
            cmd.Transaction = dbTransaction;
            cmd.CommandText = sqlStr;
            foreach (var item in p)
            {
                var _p = cmd.CreateParameter();
                _p.ParameterName = item.Key;
                var val = item.Value;
                if (val == null)
                {
                    _p.Value = DBNull.Value;
                    _p.DbType = DbType.String;
                    _p.Size = 1;
                }
                else
                {
                    _p.Value = val;
                    _p.DbType = TypeToDbType(val.GetType());
                    if (_p.DbType == DbType.String)
                    {
                        var val_str = val!.ToString();
                        _p.Size = Math.Max(val_str!.ToString()!.Length, 1);
                    }
                    if(_p.DbType == DbType.Decimal)
                    {
                        var val_str = (decimal)val;
                        _p.Scale = 10;
                        _p.Precision = 30;
                    }
                }
                cmd.Parameters.Add(_p);
            }
            cmd.Prepare();
            return cmd;
        }
        private DbType TypeToDbType(Type t)
        {
            DbType dbt;
            try
            {
                dbt = (DbType)Enum.Parse(typeof(DbType), t.Name);
            }
            catch
            {
                dbt = DbType.Object;
            }
            return dbt;
        }

        public void Commit()
        {
            if (dbTransaction != null)
            {
                try
                {
                    dbTransaction.Commit();
                    dbTransaction.Dispose();

                }
                catch (Exception ex)
                {
                    throw new MyException(ex);
                }
                finally
                {
                    dbTransaction = null;
                }
            }
        }
        public void Abort()
        {
            if (dbTransaction != null)
            {
                try
                {
                    dbTransaction.Rollback();
                    dbTransaction.Dispose();
                }
                catch (Exception ex)
                {
                    throw new MyException(ex);
                }
                finally
                {
                    dbTransaction = null;
                }
            }
        }

        public void Dispose()
        {
            Commit();
            Close();
        }
    }
}
